package dbreflection.tablemanagement;

import dbreflection.dbanalisys.DBScheme;
import dbreflection.tablemanagement.ColumnValuePair;
import dbreflection.ConnectionManager;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * User: [J0k3r]
 * Date: 14.11.2008
 * Time: 3:56:12
 */
public class TableManager
{
    private DBScheme scheme = null;
    private String table = null;

    public TableManager(DBScheme scheme, String table) {
        this.table = table;
        this.scheme = scheme;
    }

    public Integer createRecord(List<ColumnValuePair> columnValuePairs) throws SQLException {
        String cols = "";
        String values = "";

        ArrayList< Object > objects = new ArrayList<Object>();
        for (ColumnValuePair o : columnValuePairs) {
            cols += cols.length() > 0 ? ", " + o.getColumn() : o.getColumn();
            values += values.length() > 0 ? ", ?" : "?";
            objects.add(o.getValue());
        }

        PreparedStatement statement = ConnectionManager.getConnection(scheme.getSchemeName()).prepareStatement(
                "insert into " + table + " (" + cols + ") values (" + values + ")");
        for (Object object : objects)
            statement.setObject(objects.indexOf(object) + 1, object);
        statement.execute();

        statement.close();

        Integer id = null;
        if (scheme.getTable(table).getColumn("id") != null) {
            statement = ConnectionManager.getConnection(scheme.getSchemeName()).prepareStatement("select max(id) as maxid from " + table);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next())
                id = resultSet.getInt("maxid");
            resultSet.close();
            statement.close();
        }
        return id;
    }

    public void deleteRecord(List< ColumnValuePair> columnValuePairs) throws SQLException {
        String cols = "";

        ArrayList< Object > objects = new ArrayList<Object>();
        for (ColumnValuePair o : columnValuePairs) {
            cols += cols.length() > 0 ? " and " + o.getColumn() + " = ?": o.getColumn() + " = ?";
            objects.add(o.getValue());
        }

        PreparedStatement statement = ConnectionManager.getConnection(scheme.getSchemeName()).prepareStatement(
                "delete from " + table + " where " + cols);
        for (Object object : objects)
            statement.setObject(objects.indexOf(object) + 1, object);
        statement.execute();

        statement.close();
    }

    public List < Object[] > getRecords(List < String > columns2Get, List <ColumnValuePair> columnValuePairs) throws SQLException {
        String where = "";

        ArrayList< Object > objects = new ArrayList<Object>();
        if (columnValuePairs != null)
            for (ColumnValuePair o : columnValuePairs) {
                where += where.length() > 0 ? " and " + o.getColumn() + " = ?": o.getColumn() + " = ?";
                objects.add(o.getValue());
            }

        String cols = "";
        for (String s : columns2Get) {
            cols += cols.length() > 0 ? ", " + s : s;
        }

        PreparedStatement statement = ConnectionManager.getConnection(scheme.getSchemeName()).prepareStatement(
                "select " + cols + " from " + table + (((columnValuePairs != null) && (columnValuePairs.size() > 0)) ? " where " + where : ""));
        for (Object object : objects)
            statement.setObject(objects.indexOf(object) + 1, object);

        ArrayList < Object[] > result = new ArrayList<Object[]>();
        ResultSet resultSet = statement.executeQuery();

        while (resultSet.next()) {
            Object[] recData = new Object[resultSet.getMetaData().getColumnCount()];
            for (int nI = 1; nI <= recData.length; ++nI)
                recData[nI - 1] = resultSet.getObject(nI);
            result.add(recData);
        }

        resultSet.close();
        statement.close();

        return result;
    }

    public Object[] getRecord(List < String > columns2Get, List <ColumnValuePair> columnValuePairs) throws SQLException {
        String where = "";

        ArrayList< Object > objects = new ArrayList<Object>();
        if (columnValuePairs != null)
            for (ColumnValuePair o : columnValuePairs) {
                where += where.length() > 0 ? " and " + o.getColumn() + " = ?": o.getColumn() + " = ?";
                objects.add(o.getValue());
            }

        String cols = "";
        for (String s : columns2Get) {
            cols += cols.length() > 0 ? ", " + s : s;
        }

        PreparedStatement statement = ConnectionManager.getConnection(scheme.getSchemeName()).prepareStatement(
                "select " + cols + " from " + table + (((columnValuePairs != null) && (columnValuePairs.size() > 0)) ? " where " + where : ""));
        for (Object object : objects)
            statement.setObject(objects.indexOf(object) + 1, object);

        Object[] result = null;
        ResultSet resultSet = statement.executeQuery();

        if (resultSet.next()) {
            result = new Object[resultSet.getMetaData().getColumnCount()];
            for (int nI = 1; nI <= result.length; ++nI)
                result[nI - 1] = resultSet.getObject(nI);
        }

        resultSet.close();
        statement.close();

        return result;
    }
}
